{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as  pd\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "file_path = 'D:/gitcode/PPJUtils/utils/citycountry_lonlat.txt'\n",
    "obj_path =  'D:/gitcode/PPJUtils/utils/citycountry_lonlat_fixed.txt'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "citycountry_lonlat = pd.read_table(file_path, header= -1, encoding='utf-8', sep = ' ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "citycountry_lonlat.columns = [\"countryname\", \"lon\", \"lat\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>countryname</th>\n",
       "      <th>lon</th>\n",
       "      <th>lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>上海上海</td>\n",
       "      <td>121.48</td>\n",
       "      <td>31.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>上海嘉定</td>\n",
       "      <td>121.24</td>\n",
       "      <td>31.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>上海宝山</td>\n",
       "      <td>121.48</td>\n",
       "      <td>31.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>上海川沙</td>\n",
       "      <td>121.7</td>\n",
       "      <td>31.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>上海南汇</td>\n",
       "      <td>121.76</td>\n",
       "      <td>31.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>上海奉贤</td>\n",
       "      <td>121.46</td>\n",
       "      <td>30.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>上海松江</td>\n",
       "      <td>121.24</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>上海金山</td>\n",
       "      <td>121.16</td>\n",
       "      <td>30.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>上海青浦</td>\n",
       "      <td>121.1</td>\n",
       "      <td>31.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>上海崇明</td>\n",
       "      <td>121.4</td>\n",
       "      <td>31.73</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  countryname     lon    lat\n",
       "0        上海上海  121.48  31.22\n",
       "1        上海嘉定  121.24   31.4\n",
       "2        上海宝山  121.48  31.41\n",
       "3        上海川沙   121.7  31.19\n",
       "4        上海南汇  121.76  31.05\n",
       "5        上海奉贤  121.46  30.92\n",
       "6        上海松江  121.24     31\n",
       "7        上海金山  121.16  30.89\n",
       "8        上海青浦   121.1  31.15\n",
       "9        上海崇明   121.4  31.73"
      ]
     },
     "execution_count": 235,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "citycountry_lonlat.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** 看哪种切割方式，如果是用：分隔，则新变成4列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# citycountry_lonlat = citycountry_lonlat.drop(\"city\", axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 解析出城市名称\n",
    "citycountry_lonlat.countryname = citycountry_lonlat.countryname.apply(lambda x: x.split(':')[1] if len(x.split(':'))>1 else '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'121.48'"
      ]
     },
     "execution_count": 232,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#解析出经度\n",
    "citycountry_lonlat.lon[2].split(\":\")[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 解析出经度\n",
    "citycountry_lonlat.lon = citycountry_lonlat.lon.apply(lambda x: x.split(':')[1] if len(x.split(':'))>1 else '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 解析出维度\n",
    "citycountry_lonlat['lat']=citycountry_lonlat.lat.apply(lambda x: x.split(':')[1] if len(x.split(':'))>1 else '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 把整理好的文件输出来；\n",
    "citycountry_lonlat.to_csv(obj_path, sep = '\\t', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 利用正则方式来替换不需要的文件；\n",
    "def trim_text(x):\n",
    "    if len(x)>1:\n",
    "        pattern = re.compile(r'^[城市:|^经度:|^维度:]*')\n",
    "        y = re.sub(pattern,\"\",x)\n",
    "        return y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'121.48'"
      ]
     },
     "execution_count": 218,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample = '经度:121.48'\n",
    "trim_text(sample)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       121.48\n",
       "1       121.24\n",
       "2       121.48\n",
       "3        121.7\n",
       "4       121.76\n",
       "5       121.46\n",
       "6       121.24\n",
       "7       121.16\n",
       "8        121.1\n",
       "9        121.4\n",
       "10      102.73\n",
       "11      102.48\n",
       "12      102.58\n",
       "13      102.79\n",
       "14      102.44\n",
       "15       103.7\n",
       "16      103.63\n",
       "17      103.91\n",
       "18      104.06\n",
       "19      103.54\n",
       "20      103.97\n",
       "21      104.28\n",
       "22      105.05\n",
       "23      104.86\n",
       "24      102.92\n",
       "25      104.38\n",
       "26      103.79\n",
       "27      104.09\n",
       "28      104.24\n",
       "29      103.97\n",
       "         ...  \n",
       "2230    131.12\n",
       "2231     130.5\n",
       "2232    129.47\n",
       "2233    129.35\n",
       "2234    126.95\n",
       "2235    126.58\n",
       "2236    127.38\n",
       "2237    127.48\n",
       "2238    128.03\n",
       "2239     128.7\n",
       "2240     128.8\n",
       "2241    128.35\n",
       "2242    127.95\n",
       "2243    127.17\n",
       "2244    126.32\n",
       "2245     124.4\n",
       "2246    124.85\n",
       "2247    125.87\n",
       "2248    126.22\n",
       "2249    126.07\n",
       "2250     125.3\n",
       "2251    124.87\n",
       "2252    123.45\n",
       "2253    123.18\n",
       "2254    123.48\n",
       "2255    124.44\n",
       "2256    124.07\n",
       "2257     126.6\n",
       "2258     124.7\n",
       "2259    122.37\n",
       "Name: lon, dtype: object"
      ]
     },
     "execution_count": 223,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "citycountry_lonlat.lon.apply(trim_text)"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [Root]",
   "language": "python",
   "name": "Python [Root]"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
